package com.education.ai.dao.impl;

import com.education.ai.dao.ExamRecordDao;
import com.education.ai.entity.ExamRecord;
import com.education.ai.util.JdbcUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.sql.*;
import java.util.*;

/**
 * 考试记录数据访问实现类
 */
@Repository
public class ExamRecordDaoImpl implements ExamRecordDao {

    private static final Logger logger = LoggerFactory.getLogger(ExamRecordDaoImpl.class);
    
    @Autowired
    private JdbcUtil jdbcUtil;
    
    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    @Override
    public List<ExamRecord> findAll() {
        String sql = "SELECT * FROM exam_records";
        return mapResultsToExamRecords(jdbcUtil.executeQuery(sql));
    }
    
    @Override
    public Optional<ExamRecord> findById(Long id) {
        String sql = "SELECT * FROM exam_records WHERE id = ?";
        List<Map<String, Object>> results = jdbcUtil.executeQuery(sql, id);
        return results.isEmpty() ? Optional.empty() : Optional.of(mapRowToExamRecord(results.get(0)));
    }
    
    @Override
    public List<ExamRecord> findByStudentId(String studentId) {
        String sql = "SELECT * FROM exam_records WHERE student_id = ? ORDER BY exam_time DESC";
        return mapResultsToExamRecords(jdbcUtil.executeQuery(sql, studentId));
    }
    
    @Override
    public List<ExamRecord> findByStudentIdIn(List<String> studentIds) {
        if (studentIds == null || studentIds.isEmpty()) {
            return Collections.emptyList();
        }
        
        // 构建IN子句的占位符
        StringBuilder placeholders = new StringBuilder();
        for (int i = 0; i < studentIds.size(); i++) {
            placeholders.append(i == 0 ? "?" : ", ?");
        }
        
        String sql = String.format("SELECT * FROM exam_records WHERE student_id IN (%s) ORDER BY student_id, exam_time DESC", placeholders);
        
        // 将studentIds转换为Object数组
        Object[] params = studentIds.toArray();
        
        return mapResultsToExamRecords(jdbcUtil.executeQuery(sql, params));
    }
    
    @Override
    public Map<String, Double> findAverageScoresByStudentId(String studentId) {
        String sql = "SELECT " +
                "AVG(chinese) AS chinese_avg, " +
                "AVG(math) AS math_avg, " +
                "AVG(english) AS english_avg, " +
                "AVG(politics) AS politics_avg, " +
                "AVG(history) AS history_avg, " +
                "AVG(geography) AS geography_avg, " +
                "AVG(physics) AS physics_avg, " +
                "AVG(chemistry) AS chemistry_avg, " +
                "AVG(biology) AS biology_avg, " +
                "AVG(total_score) AS total_avg " +
                "FROM exam_records WHERE student_id = ?";
        
        List<Map<String, Object>> results = jdbcUtil.executeQuery(sql, studentId);
        
        if (results.isEmpty()) {
            return Collections.emptyMap();
        }
        
        Map<String, Object> row = results.get(0);
        Map<String, Double> averages = new HashMap<>();
        
        averages.put("chinese", getDoubleValue(row, "chinese_avg"));
        averages.put("math", getDoubleValue(row, "math_avg"));
        averages.put("english", getDoubleValue(row, "english_avg"));
        averages.put("politics", getDoubleValue(row, "politics_avg"));
        averages.put("history", getDoubleValue(row, "history_avg"));
        averages.put("geography", getDoubleValue(row, "geography_avg"));
        averages.put("physics", getDoubleValue(row, "physics_avg"));
        averages.put("chemistry", getDoubleValue(row, "chemistry_avg"));
        averages.put("biology", getDoubleValue(row, "biology_avg"));
        averages.put("total", getDoubleValue(row, "total_avg"));
        
        return averages;
    }
    
    @Override
    public ExamRecord save(ExamRecord examRecord) {
        if (examRecord.getId() == null) {
            // 插入操作
            String sql = "INSERT INTO exam_records (student_id, exam_name, exam_time, exam_date, chinese, math, english, " +
                    "politics, history, geography, physics, chemistry, biology, total_score, class_rank, grade_rank) " +
                    "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
            
            long id = jdbcUtil.executeInsert(sql,
                    examRecord.getStudentId(),
                    examRecord.getExamName(),
                    examRecord.getExamTime(),
                    examRecord.getExamDate(),
                    examRecord.getChinese(),
                    examRecord.getMath(),
                    examRecord.getEnglish(),
                    examRecord.getPolitics(),
                    examRecord.getHistory(),
                    examRecord.getGeography(),
                    examRecord.getPhysics(),
                    examRecord.getChemistry(),
                    examRecord.getBiology(),
                    examRecord.getTotalScore(),
                    examRecord.getClassRank(),
                    examRecord.getGradeRank());
            
            examRecord.setId(id);
            return examRecord;
        } else {
            // 更新操作
            update(examRecord);
            return examRecord;
        }
    }
    
    @Override
    public List<ExamRecord> saveAll(List<ExamRecord> examRecords) {
        if (examRecords == null || examRecords.isEmpty()) {
            return Collections.emptyList();
        }
        
        // 使用事务批量插入
        return jdbcUtil.executeTransaction(conn -> {
            try {
                PreparedStatement stmt = conn.prepareStatement(
                        "INSERT INTO exam_records (student_id, exam_name, exam_time, exam_date, chinese, math, english, " +
                        "politics, history, geography, physics, chemistry, biology, total_score, class_rank, grade_rank) " +
                        "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
                        Statement.RETURN_GENERATED_KEYS);
                
                for (ExamRecord record : examRecords) {
                    stmt.setString(1, record.getStudentId());
                    stmt.setString(2, record.getExamName());
                    stmt.setDate(3, record.getExamTime());
                    stmt.setString(4, record.getExamDate());
                    setDoubleOrNull(stmt, 5, record.getChinese());
                    setDoubleOrNull(stmt, 6, record.getMath());
                    setDoubleOrNull(stmt, 7, record.getEnglish());
                    setDoubleOrNull(stmt, 8, record.getPolitics());
                    setDoubleOrNull(stmt, 9, record.getHistory());
                    setDoubleOrNull(stmt, 10, record.getGeography());
                    setDoubleOrNull(stmt, 11, record.getPhysics());
                    setDoubleOrNull(stmt, 12, record.getChemistry());
                    setDoubleOrNull(stmt, 13, record.getBiology());
                    setDoubleOrNull(stmt, 14, record.getTotalScore());
                    setIntegerOrNull(stmt, 15, record.getClassRank());
                    setIntegerOrNull(stmt, 16, record.getGradeRank());
                    
                    stmt.addBatch();
                }
                
                // 执行批处理
                stmt.executeBatch();
                
                // 获取自动生成的ID
                try (ResultSet generatedKeys = stmt.getGeneratedKeys()) {
                    int i = 0;
                    while (generatedKeys.next() && i < examRecords.size()) {
                        examRecords.get(i).setId(generatedKeys.getLong(1));
                        i++;
                    }
                }
                
                stmt.close();
                return examRecords;
            } catch (SQLException e) {
                logger.error("批量保存考试记录失败: {}", e.getMessage(), e);
                throw new RuntimeException("批量保存考试记录失败", e);
            }
        });
    }
    
    @Override
    public boolean update(ExamRecord examRecord) {
        String sql = "UPDATE exam_records SET student_id = ?, exam_name = ?, exam_time = ?, exam_date = ?, " +
                "chinese = ?, math = ?, english = ?, politics = ?, history = ?, geography = ?, " +
                "physics = ?, chemistry = ?, biology = ?, total_score = ?, class_rank = ?, grade_rank = ? " +
                "WHERE id = ?";
        
        int rows = jdbcUtil.executeUpdate(sql,
                examRecord.getStudentId(),
                examRecord.getExamName(),
                examRecord.getExamTime(),
                examRecord.getExamDate(),
                examRecord.getChinese(),
                examRecord.getMath(),
                examRecord.getEnglish(),
                examRecord.getPolitics(),
                examRecord.getHistory(),
                examRecord.getGeography(),
                examRecord.getPhysics(),
                examRecord.getChemistry(),
                examRecord.getBiology(),
                examRecord.getTotalScore(),
                examRecord.getClassRank(),
                examRecord.getGradeRank(),
                examRecord.getId());
        
        return rows > 0;
    }
    
    @Override
    public boolean deleteById(Long id) {
        String sql = "DELETE FROM exam_records WHERE id = ?";
        int rows = jdbcUtil.executeUpdate(sql, id);
        return rows > 0;
    }
    
    @Override
    public long count() {
        String sql = "SELECT COUNT(*) AS count FROM exam_records";
        List<Map<String, Object>> results = jdbcUtil.executeQuery(sql);
        if (!results.isEmpty()) {
            return ((Number) results.get(0).get("count")).longValue();
        }
        return 0;
    }
    
    /**
     * 将查询结果映射为考试记录对象列表
     */
    private List<ExamRecord> mapResultsToExamRecords(List<Map<String, Object>> results) {
        List<ExamRecord> records = new ArrayList<>();
        for (Map<String, Object> row : results) {
            records.add(mapRowToExamRecord(row));
        }
        return records;
    }
    
    /**
     * 将单行结果映射为考试记录对象
     */
    private ExamRecord mapRowToExamRecord(Map<String, Object> row) {
        ExamRecord record = new ExamRecord();
        record.setId(((Number) row.get("id")).longValue());
        record.setStudentId((String) row.get("student_id"));
        record.setExamName((String) row.get("exam_name"));
        
        // 处理日期时间类型
        if (row.get("exam_time") != null) {
            Object timeObj = row.get("exam_time");
            if (timeObj instanceof java.sql.Date) {
                record.setExamTime((java.sql.Date) timeObj);
            } else if (timeObj instanceof java.sql.Timestamp) {
                // 将Timestamp转换为java.sql.Date
                record.setExamTime(new java.sql.Date(((java.sql.Timestamp) timeObj).getTime()));
            } else if (timeObj instanceof java.util.Date) {
                // 将java.util.Date转换为java.sql.Date
                record.setExamTime(new java.sql.Date(((java.util.Date) timeObj).getTime()));
            } else {
                logger.warn("无法识别的考试时间类型: {}, 值: {}", timeObj.getClass().getName(), timeObj);
            }
        }
        
        record.setExamDate((String) row.get("exam_date"));
        
        // 设置科目成绩
        record.setChinese(row.get("chinese") != null ? ((Number) row.get("chinese")).doubleValue() : null);
        record.setMath(row.get("math") != null ? ((Number) row.get("math")).doubleValue() : null);
        record.setEnglish(row.get("english") != null ? ((Number) row.get("english")).doubleValue() : null);
        record.setPolitics(row.get("politics") != null ? ((Number) row.get("politics")).doubleValue() : null);
        record.setHistory(row.get("history") != null ? ((Number) row.get("history")).doubleValue() : null);
        record.setGeography(row.get("geography") != null ? ((Number) row.get("geography")).doubleValue() : null);
        record.setPhysics(row.get("physics") != null ? ((Number) row.get("physics")).doubleValue() : null);
        record.setChemistry(row.get("chemistry") != null ? ((Number) row.get("chemistry")).doubleValue() : null);
        record.setBiology(row.get("biology") != null ? ((Number) row.get("biology")).doubleValue() : null);
        
        record.setTotalScore(row.get("total_score") != null ? ((Number) row.get("total_score")).doubleValue() : null);
        record.setClassRank(row.get("class_rank") != null ? ((Number) row.get("class_rank")).intValue() : null);
        record.setGradeRank(row.get("grade_rank") != null ? ((Number) row.get("grade_rank")).intValue() : null);
        
        return record;
    }
    
    /**
     * 安全获取Double值
     */
    private Double getDoubleValue(Map<String, Object> row, String key) {
        if (row.get(key) == null) return null;
        return ((Number) row.get(key)).doubleValue();
    }
    
    /**
     * 设置Double或NULL值
     */
    private void setDoubleOrNull(PreparedStatement stmt, int index, Double value) throws SQLException {
        if (value == null) {
            stmt.setNull(index, Types.DOUBLE);
        } else {
            stmt.setDouble(index, value);
        }
    }
    
    /**
     * 设置Integer或NULL值
     */
    private void setIntegerOrNull(PreparedStatement stmt, int index, Integer value) throws SQLException {
        if (value == null) {
            stmt.setNull(index, Types.INTEGER);
        } else {
            stmt.setInt(index, value);
        }
    }
} 